﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace CMS1 {
    public class DatabaseWrapper {

        public static string connString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\macmsdb.mdf;Integrated Security=True";
        SqlConnection cn;
        SqlCommand cmd = new SqlCommand();
        SqlDataReader dr;
        private string query = "";

        const int NO_SELECT = 0;
        const int MANUAL_SELECT = 1;
        const int SELECT_COLUMN = 2;
        const int SELECT_ROW = 3;
        const int SELECT_CELL = 4;

        private int selectState;

        public void initConnection(string connString) {
            cn = new SqlConnection(connString);
            cmd.Connection = cn;
        }

        public DatabaseWrapper select(string columns) {
            if (selectState == NO_SELECT) {
                selectState = MANUAL_SELECT;
            }
            query = "select " + columns + " ";
            return this;
        }
        public DatabaseWrapper selectAll() {
            if (selectState == NO_SELECT) {
                selectState = MANUAL_SELECT;
            }
            query = "select * ";
            return this;
        }
        public DatabaseWrapper from(string table) {
            query += "from " + table + " ";
            return this;
        }
        public DatabaseWrapper insert_into(string table) {
            query = "insert into " + table + " ";
            return this;
        }
        public DatabaseWrapper columns(string columns) {
            query += "(" + columns + ") ";
            return this;
        }
        public DatabaseWrapper values(string values) {
            query += "values (" + values + ") ";
            return this;
        }
        public DatabaseWrapper update(string table) {
            query = "update " + table + " set ";
            return this;
        }
        public DatabaseWrapper set(string column, string value) {
            query += column + " = '" + value + "',";
            return this;
        }
        public DatabaseWrapper where(string condition) {
            query += "where " + condition + " ";
            return this;
        }
        public DatabaseWrapper and(string condition) {
            query += " and " + condition + " ";
            return this;
        }
        public DatabaseWrapper or(string condition) {
            query += " or " + condition + " ";
            return this;
        }
        public DatabaseWrapper selectColumn(string column) {
            selectState = SELECT_COLUMN;
            select(column);
            return this;
        }
        public DatabaseWrapper selectRowFrom(string table) {
            selectState = SELECT_ROW;
            selectAll().from(table);
            return this;
        }
        public DatabaseWrapper selectCell(string column) {
            selectState = SELECT_CELL;
            query = "select " + column + " ";
            return this;
        }

        public DatabaseWrapper delete_from(string table) {
            query = "delete from " + table + " ";
            return this;
        }

        public object execute() {
            DataSet ds = null;
            SqlDataAdapter dataCommand = null;
            object select = null;
            if (selectState != NO_SELECT) {
                cn.Open();
                ds = new DataSet();
                //string SelectCommand = "SELECT * FROM myTable WHERE Value = 100";
                dataCommand = new SqlDataAdapter(query, cn);
                //Get data from a server and fill the DataSet  
                dataCommand.Fill(ds);
                cn.Close();
                if (ds.Tables[0].Rows.Count == 0) {
                    return null;
                }
            }
            switch (selectState) {
                case MANUAL_SELECT:
                    object[][] rows = new object[ds.Tables[0].Rows.Count][];
                    int i = 0;
                    foreach (DataRow row in ds.Tables[0].Rows) {
                        /*List<object> columns = new List<object>();
                        foreach (DataColumn column in ds.Tables[0].Columns) {
                            columns.Add(row[column]);
                        }
                        rows[0] = columns.ToArray();*/
                        rows[i++] = row.ItemArray.ToArray();
                    }
                    select = rows;
                    break;
                case SELECT_COLUMN:
                    List<object> columnElements = new List<object>();
                    foreach (DataRow row in ds.Tables[0].Rows) {
                        columnElements.Add(row[ds.Tables[0].Columns[0]]);
                        /*foreach (DataColumn column in ds.Tables[0].Columns) {
                            columnElements.Add(row[column]);
                        }*/
                    }
                    select = columnElements.ToArray();
                    break;
                case SELECT_ROW:
                    select = ds.Tables[0].Rows[0].ItemArray;
                    break;
                case SELECT_CELL:
                    select = ds.Tables[0].Rows[0][0];
                    break;
                case NO_SELECT:
                    if (query[query.Length - 1] == ',') {
                        query = query.Substring(0, query.Length - 1);
                    }
                    cn.Open();
                    cmd.CommandText = query;
                    System.Diagnostics.Debug.WriteLine(query);
                    cmd.ExecuteNonQuery();
                    cn.Close();
                    break;
            }
            selectState = NO_SELECT;
            query = "";
            return select;
        }
    }
}
/*****************************************************
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++) {
                    for (int k = 0; k < ds.Tables[0].Columns.Count; k++) {
                        object o = ds.Tables[0].Rows[i][k];
                    }
                }
 ****************************************************/

/****************************************************
cn.Open();
cmd.CommandText = "select * from table";
dr = cmd.ExecuteReader();
if (dr.HasRows) {
    //LinkedList<string> elements = new LinkedList<string>();
    rows = new List<object[]>();
    while (dr.Read()) {
        object[] row = new object[dr.FieldCount];
        dr.GetValues(row);
        rows.Add(row);
        //elements.AddLast(dr[0].ToString());
    }
}
cn.Close();*/